﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;

namespace Agresso.Interface.Layer.Service
{
    public class WinSDSService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        DataBaseConnectivity m_winsdsInterfacedataBaseConectivity;
        AlgBatcInputService m_AlgBatcInputService;
        VesselService m_vesselService;
        ConfigurationSettingService m_configSettingService;
        AlertService m_alertService;
        agldimvalueService m_agldimvalueService;
        c_GRN_Service m_c_GRN_Service;
        asuheaderService m_asuheaderService;
        ud_algbatchinput_remarkService m_ud_algbatchinput_remarkService;

        public WinSDSService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_winsdsInterfacedataBaseConectivity = new DataBaseConnectivity();
            m_AlgBatcInputService = new AlgBatcInputService();
            m_configSettingService = new ConfigurationSettingService();
            m_vesselService = new VesselService();
            m_agldimvalueService = new agldimvalueService();
            m_alertService = new AlertService();
            m_c_GRN_Service = new c_GRN_Service();
            m_asuheaderService = new asuheaderService();
            m_ud_algbatchinput_remarkService = new ud_algbatchinput_remarkService();
        }
        public IList RetrieveAttachmentDataList()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetAttachmentList(), m_dataBaseConectivity.WinSDSSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new jPOAttachments(rd));
                }
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
            }
        }
        public IList RetrieveAllPOData()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.WinSDSSqlConnection_b.Open();
                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetAllPOList(), m_dataBaseConectivity.WinSDSSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    jPOHeader po = new jPOHeader(rd);

                    SqlCommand cmd_details = new SqlCommand(WinSDSSQL.GetAllPODetailByPONumber(), m_dataBaseConectivity.WinSDSSqlConnection_b);
                    cmd_details.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = po.PoNo;
                    SqlDataReader rd_detail = cmd_details.ExecuteReader();
                    while (rd_detail.Read())
                    {
                        JPODetails detail = new JPODetails(rd_detail);
                        po.Details.Add(detail);
                    }
                    rd_detail.Close();

                    SqlCommand cmd_attachement = new SqlCommand(WinSDSSQL.GetAttachmentByPO(), m_dataBaseConectivity.WinSDSSqlConnection_b);
                    cmd_attachement.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = po.PoNo;
                    SqlDataReader rd_att = cmd_attachement.ExecuteReader();
                    while (rd_att.Read())
                    {
                        jPOAttachments att = new jPOAttachments(rd_att);
                        po.Attachments.Add(att);
                    }
                    rd_att.Close();

                    result.Add(po);
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.WinSDSSqlConnection_b.Close();
            }
        }
        public IList RetrieveAllPOData(string searchPONo, bool pickupFlag)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.WinSDSSqlConnection_b.Open();
                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetAllPOList(searchPONo, pickupFlag), m_dataBaseConectivity.WinSDSSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    jPOHeader po = new jPOHeader(rd);

                    SqlCommand cmd_details = new SqlCommand(WinSDSSQL.GetAllPODetailByPONumber(), m_dataBaseConectivity.WinSDSSqlConnection_b);
                    cmd_details.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = po.PoNo;
                    SqlDataReader rd_detail = cmd_details.ExecuteReader();
                    while (rd_detail.Read())
                    {
                        JPODetails detail = new JPODetails(rd_detail);
                        po.Details.Add(detail);
                    }
                    rd_detail.Close();

                    SqlCommand cmd_attachement = new SqlCommand(WinSDSSQL.GetAttachmentByPO(), m_dataBaseConectivity.WinSDSSqlConnection_b);
                    cmd_attachement.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = po.PoNo;
                    SqlDataReader rd_att = cmd_attachement.ExecuteReader();
                    while (rd_att.Read())
                    {
                        jPOAttachments att = new jPOAttachments(rd_att);
                        po.Attachments.Add(att);
                    }
                    rd_att.Close();

                    result.Add(po);
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.WinSDSSqlConnection_b.Close();
            }
        }
        public void ImportSupplierDataFromAgressoToDMZ()
        {
            try
            {
                IList suppliers = new ArrayList();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                m_dataBaseConectivity.WinSDSSqlConnection.Open();

                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetAllSupplierAgresso(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    suppliers.Add(new c_supplier(rd));
                }
                rd.Close();
                foreach (c_supplier supp in suppliers)
                {
                    cmd.CommandText = WinSDSSQL.SelectCountJVendorByVendorID();
                    cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@VendorId", System.Data.SqlDbType.NVarChar).Value = supp.apar_id;
                    int count = Convert.ToInt16(cmd.ExecuteScalar());
                    if (count == 0)
                    {
                        jVendors vendor = new jVendors(supp);
                        cmd.CommandText = WinSDSSQL.InsertJVendors();
                        cmd.Parameters.Clear();
                        //if vendor address more than 80 char then cut off up to 80; 
                        vendor.VendorAddress1 = vendor.VendorAddress1.Length > 80 ? vendor.VendorAddress1.Substring(0, 80) : vendor.VendorAddress1;
                        vendor.VendorName = vendor.VendorName.Length > 80 ? vendor.VendorName.Substring(0, 80) : vendor.VendorName;
                        cmd.Parameters.Add("@Client", System.Data.SqlDbType.NVarChar).Value = vendor.Client;
                        cmd.Parameters.Add("@VendorId", System.Data.SqlDbType.NVarChar).Value = vendor.VendorId;
                        cmd.Parameters.Add("@VendorName", System.Data.SqlDbType.NVarChar).Value = vendor.VendorName;
                        cmd.Parameters.Add("@CurrencyCode", System.Data.SqlDbType.NVarChar).Value = vendor.CurrencyCode;
                        cmd.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar).Value = vendor.Country;
                        cmd.Parameters.Add("@VendorAddress1", System.Data.SqlDbType.NVarChar).Value = vendor.VendorAddress1;
                        cmd.Parameters.Add("@VendorAddress2", System.Data.SqlDbType.NVarChar).Value = vendor.VendorAddress2;
                        cmd.Parameters.Add("@VendorPhone", System.Data.SqlDbType.NVarChar).Value = vendor.VendorPhone;
                        cmd.Parameters.Add("@VendorFax", System.Data.SqlDbType.NVarChar).Value = vendor.VendorFax;
                        cmd.Parameters.Add("@VendorEmailID", System.Data.SqlDbType.NVarChar).Value = vendor.VendorEmailID;
                        cmd.Parameters.Add("@PaymentTerms", System.Data.SqlDbType.NVarChar).Value = vendor.PaymentTerms;
                        cmd.Parameters.Add("@PaymentMethod", System.Data.SqlDbType.NVarChar).Value = vendor.PaymentMethod;
                        cmd.Parameters.Add("@EnabledFlag", System.Data.SqlDbType.NVarChar).Value = vendor.EnabledFlag;
                        cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime).Value = vendor.AddedOn;
                        cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.NVarChar).Value = vendor.PickedUpFlag;
                       // cmd.Parameters.Add("@PickedUpAt", System.Data.SqlDbType.DateTime).Value = vendor.PickedUpAt;
                        cmd.Parameters.Add("@ShortName", System.Data.SqlDbType.NVarChar).Value = vendor.ShortName;
                        cmd.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar).Value = vendor.Description;
                        cmd.Parameters.Add("@ResultDetails", System.Data.SqlDbType.NVarChar).Value = vendor.ResultDetails;
                        cmd.Parameters.Add("@ExecutionID", System.Data.SqlDbType.NVarChar).Value = vendor.ExecutionID;
                        cmd.ExecuteNonQuery();

                        cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                        cmd.CommandText = WinSDSSQL.UpdatePickupFlagSupplierAgresso();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@apar_id", System.Data.SqlDbType.NVarChar).Value = supp.apar_id;
                        cmd.Parameters.Add("@pickedupflag", System.Data.SqlDbType.Int).Value = 1;
                        cmd.Parameters.Add("@PickedUpDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                    else// if found but not updated........do this also
                    {
                        cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                        cmd.CommandText = WinSDSSQL.UpdatePickupFlagSupplierAgresso();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@apar_id", System.Data.SqlDbType.NVarChar).Value = supp.apar_id;
                        cmd.Parameters.Add("@pickedupflag", System.Data.SqlDbType.Int).Value = 1;
                        cmd.Parameters.Add("@PickedUpDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
            }

        }
        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }

       // int test = 0;        
        public void ExecuteInterfaceAllPOData()
        {
            IList allPO = new ArrayList();
            try
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.WinSDSSqlConnection_b.Open();
                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetAllPendingPOList(), m_dataBaseConectivity.WinSDSSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    jPOHeader po = new jPOHeader(rd);
                    SqlCommand cmd_details = new SqlCommand(WinSDSSQL.GetAllPODetailByPONumber(), m_dataBaseConectivity.WinSDSSqlConnection_b);
                    cmd_details.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = po.PoNo;
                    SqlDataReader rd_detail = cmd_details.ExecuteReader();
                    while (rd_detail.Read())
                    {
                        JPODetails detail = new JPODetails(rd_detail);
                        if(po.Details.Count < po.TotalLineItems)
                            po.Details.Add(detail);
                    }
                    rd_detail.Close();

                    SqlCommand cmd_attachement = new SqlCommand(WinSDSSQL.GetAttachmentByPO(), m_dataBaseConectivity.WinSDSSqlConnection_b);
                    cmd_attachement.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = po.PoNo;
                    SqlDataReader rd_att = cmd_attachement.ExecuteReader();
                    while (rd_att.Read())
                    {
                        jPOAttachments att = new jPOAttachments(rd_att);
                        po.Attachments.Add(att);
                    }
                    rd_att.Close();

                    if (po.TotalLineItems == po.Details.Count)
                        allPO.Add(po);
                    else
                        insertupdatePORemark(po.jPOheaderID, po.PoNo, po.PoDate, "0", "PO Line Count different with details count", po);
                }
                rd.Close();

                
                foreach (jPOHeader po in allPO)
                {
                   // test++;
                  //  if (test != 8) continue;
                    //if (po.jPOheaderID != 158) continue;
                    string alert = "";
                    agldimvalue vessel = m_agldimvalueService.GetAglDimValueVesselByDimValue(po.VslCode);
                    if (vessel == null) alert += string.Format("- Unable find Vessel with Code  '{0}' \r\n", po.VslCode);

                    agldimvalue vesselCostCentre = vessel != null ? m_agldimvalueService.GetAglDimValueCostCentreByDimVal(vessel.rel_value) : null;
                    if (vessel!=null && vesselCostCentre == null) alert += string.Format("- Unable find Cost Centre with Code  '{0}' \r\n", vessel.rel_value);

                    asuheader supplier = m_asuheaderService.GetSupplierByCodeForWinSDS(po.VendorID);
                    if (supplier == null) alert += string.Format("- Unable find Supplier with Code  '{0}' \r\n", po.VendorID);
                    
                  //  agldimvalue legalEntity = m_agldimvalueService.GetAglDimValueByDimVal(po.LegalEntity);
                //    if (legalEntity == null) alert += string.Format("- Unable find Legal Entity with Code  '{0}' \r\n", po.LegalEntity);
                    
                    string detailalert="";
                    //string prevdetailalert="";
                    IList error = new ArrayList();
                    foreach (JPODetails podetail in po.Details)
                    {
                        bool valid = validateArticleAndUOM(podetail.ItemCode, podetail.UOM, "S1");
                        if (!valid)
                        {
                            
                            detailalert = string.Format("- Unable find Article with Code : {0}, UOM : {1}, Client : {2} in Agresso  \r\n", podetail.ItemCode, podetail.UOM, "S1");
                            //if (detailalert != "")
                            //{
                            //if (prevdetailalert != detailalert)
                            //if (prevdetailalert == "")
                            //{
                            //    alert += detailalert;
                            //    prevdetailalert = detailalert;
                            //}
                            if (error.Count == 0)
                            {
                                alert += detailalert;
                                error.Add(podetail.ItemCode + podetail.UOM + "S1");                               
                            }
                            else if (!error.Contains(podetail.ItemCode + podetail.UOM + "S1"))
                            {
                                alert += detailalert;
                                error.Add(podetail.ItemCode + podetail.UOM + "S1");
                            }
                            //}
                        }
                    }
                    i_winsds_itemcode_account_mapping defaultMapping = FindByItemCOdeWinSDSAccountMapping("DEFAULT");
                    if (defaultMapping == null) alert += string.Format("- Unable find Default Product Mapping \r\n");

                    if (alert != "")
                    {
                        insertupdatePORemark(po.jPOheaderID, po.PoNo, po.PoDate, "0", alert, po);
                        continue;
                    }
               
                    IList algbatchinputList = new ArrayList();

                    algbatchinput algHeader = new algbatchinput();
                    algHeader.account = "";
                    algHeader.amount = 0;
                    algHeader.amount_set = 0;
                    algHeader.apar_id = po.VendorID;
                    algHeader.batch_id = "P1" + DateTime.Now.ToString("ddMMyyyyhhmmss");
                    algHeader.client = "S1";//15052013 legalEntity.client; //po.Client;
                    algHeader.cur_amount = 0;
                    algHeader.currency = po.CurrencyCode;
                    algHeader.deliv_addr = po.DeliveryLocation;
                    algHeader.deliv_date = po.PoDate;
                    algHeader.deliv_method = po.DeliveryMethod;
                    algHeader.deliv_terms = po.DeliveryTerms;
                    algHeader.dim_1 = "D6030"; //legalEntity.rel_value;
                    algHeader.dim_value_1 = "D6030"; //vessel.rel_value;
                    algHeader.dim_value_5 = vessel.dim_value;
                    algHeader.disc_percent = 0;
                    algHeader.discount = po.DiscountAmount;
                    algHeader.disc_percent = 0;
                    algHeader.discount = 0;
                    algHeader.exch_rate = 0;// po.CurrencyXrate;
                    algHeader.ext_ord_ref = po.PoNo;
                    algHeader.line_no = 0;
                    algHeader.long_info1 = po.POComments.Length > 120 ? po.POComments.Substring(0, 120) : po.POComments;
                    algHeader.main_apar_id = po.VendorID;
                    algHeader.order_date = po.PoDate;
                    algHeader.order_id = po.jPOheaderID;
                    algHeader.order_type = "VK";
                    algHeader.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    algHeader.rent_flag = 0;
                    algHeader.responsible = po.Responsible;
                    algHeader.responsible2 = po.Responsible2;
                    algHeader.sequence_no = 0;// GetSequenceRunningNumber();
                    algHeader.status = po.Status;
                    algHeader.text1 = po.Category;
                    algHeader.trans_type = "41";
                    algHeader.voucher_type = "P1";
                    algHeader.pay_method = po.PaymentMethod;
                    algbatchinputList.Add(algHeader);
                    //algHeader.pay_temp_id = po.PaymentTerms;// ----------------must removed 05Apr 2013 Carina

                    foreach (JPODetails podetail in po.Details)
                    {
                        algbatchinput detail = new algbatchinput();

                        i_winsds_itemcode_account_mapping accMapping = FindByItemCOdeWinSDSAccountMapping(podetail.ItemCode.Trim());
                        if (accMapping == null)
                            accMapping = FindByItemCOdeWinSDSAccountMapping("DEFAULT");
                        detail.account = accMapping.account.Trim();// "2904";
                        detail.amount = podetail.UnitPrice * podetail.OrdQty * po.CurrencyXrate;
                        detail.amount_set = 1;
                        detail.apar_id = "";
                        detail.art_descr = podetail.ItemDetail;
                        detail.article = podetail.ItemCode;
                        detail.batch_id = algHeader.batch_id;// "P1" + DateTime.Now.ToString("ddMMyyyyhhmmss");
                        detail.client = "S1";//15052013 legalEntity.client;//po.Client;
                        detail.cur_amount = podetail.UnitPrice * podetail.OrdQty;
                        detail.currency = po.CurrencyCode;
                        detail.deliv_addr = po.DeliveryLocation;
                        detail.deliv_date = po.PoDate;
                        detail.dim_1 = "D6030"; //legalEntity.rel_value;//. vessel.rel_value;
                        detail.dim_value_1 = "D6030"; //legalEntity.rel_value;//. vessel.rel_value;
                        detail.dim_2 = accMapping.itemCode.Trim().ToLower() == "default" ? podetail.ItemCode.Substring(0, 4) : accMapping.dim_2.Trim();
                        //detail.dim_3 = podetail.ItemCode.Substring(0, 4);
                        detail.dim_5 = vessel.dim_value;
                        detail.dim_6 = "603";//po.LegalEntity;
                        detail.dim_7 = po.LegalEntity; //15052013 legalEntity.dim_value;//29042013C vesselCostCentre.rel_value;
                        //detail.dim_value_1 = "";
                        detail.dim_value_5 = "";
                        detail.disc_percent = 0;
                        detail.discount = 0;
                        detail.exch_rate = 0;// po.CurrencyXrate;
                        detail.ext_ord_ref = po.PoNo;
                        detail.line_no = podetail.PoLineItemNo;
                        detail.order_date = po.PoDate;
                        detail.order_id = po.jPOheaderID;
                        detail.order_type = "VK";
                        detail.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                        detail.rent_flag = 0;
                        detail.responsible = po.Responsible;
                        detail.responsible2 = po.Responsible2;
                        //detail.sequence_no = GetSequenceRunningNumber();
                        detail.short_info = podetail.ItemRemarks;
                        detail.status = "N";
                        //detail.sup_article = podetail.ItemCode;
                        detail.text1 = po.Category;
                        detail.trans_type = "41";
                        detail.unit_code = podetail.UOM;
                        detail.unit_price = podetail.UnitPrice;
                        detail.value_1 = podetail.OrdQty;
                        detail.voucher_ref = 0;
                        detail.voucher_type = "P1";
                        algbatchinputList.Add(detail);
                    }
                    foreach (algbatchinput alg in algbatchinputList)
                    {
                        m_AlgBatcInputService.Insert(alg);
                        m_AlgBatcInputService.InsertToInterfaceTable(alg);
                    }
                   // m_ud_algbatchinput_remarkService.Insert(algHeader.client, algHeader.batch_id, algHeader.order_id, algHeader.ext_ord_ref, po.PO_Remark);

                    cmd.CommandText = WinSDSSQL.UpdatePOPickupFlag();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@PickedUpAt", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@AgressoRowId", System.Data.SqlDbType.BigInt).Value = algHeader.agrtid;
                    cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.VarChar).Value = po.PoNo;
                    cmd.ExecuteNonQuery();

                    po.AgressoRowId = algHeader.agrtid;
                    insertupdatePORemark(po.jPOheaderID, po.PoNo, po.PoDate, "1", algHeader.batch_id, po);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.WinSDSSqlConnection_b.Close();
            }
        }

        private bool validateArticleAndUOM(string article, string uom, string client)
        {
            bool result = true;
            m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
            SqlCommand cmd = new SqlCommand(AgressoSQL.GetCountArticleByUOMClient(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
            cmd.Parameters.Add("@article", System.Data.SqlDbType.NVarChar).Value = article;
            //cmd.Parameters.Add("@unit_code", System.Data.SqlDbType.NVarChar).Value = uom;
            cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = client;
            result = Convert.ToInt16(cmd.ExecuteScalar()) > 0;
            m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            return result;
        }
        private void insertupdatePORemark(int id, string poNo, DateTime poDate,  string pickeupflag, string remarks, jPOHeader jpo)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetPOInterfaceByID(), m_dataBaseConectivity.InterfaceSqlConnection_c);
                cmd.Parameters.Add("@jPOheaderID", System.Data.SqlDbType.Int).Value = id;
                i_jPOHeader po = null;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    po = new i_jPOHeader(rd);
                rd.Close();
                if (po == null)
                {
                    cmd.CommandText = WinSDSSQL.InsertPOInterface();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@jPOheaderID", System.Data.SqlDbType.Int).Value = id;
                    cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.NChar).Value = pickeupflag;
                    cmd.Parameters.Add("@PickedUpAt", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@remarks", System.Data.SqlDbType.NVarChar).Value = remarks;
                    cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = poNo;
                    cmd.Parameters.Add("@PoDate", System.Data.SqlDbType.DateTime).Value = poDate;
                    cmd.Parameters.Add("@Client", System.Data.SqlDbType.VarChar).Value = jpo.Client;
                    cmd.Parameters.Add("@LegalEntity", System.Data.SqlDbType.VarChar).Value = jpo.LegalEntity;
                    cmd.Parameters.Add("@VslCode", System.Data.SqlDbType.VarChar).Value = jpo.VslCode;
                    cmd.Parameters.Add("@Category", System.Data.SqlDbType.VarChar).Value = jpo.Category;
                    cmd.Parameters.Add("@WorkOrderID", System.Data.SqlDbType.VarChar).Value = jpo.WorkOrderID;
                    cmd.Parameters.Add("@VendorID", System.Data.SqlDbType.VarChar).Value = jpo.VendorID;
                    cmd.Parameters.Add("@CurrencyCode", System.Data.SqlDbType.VarChar).Value = jpo.CurrencyCode;
                    cmd.Parameters.Add("@CurrencyXrate", System.Data.SqlDbType.Float).Value = jpo.CurrencyXrate;
                    cmd.Parameters.Add("@PaymentTerms", System.Data.SqlDbType.VarChar).Value = jpo.PaymentTerms;
                    cmd.Parameters.Add("@PaymentMethod", System.Data.SqlDbType.VarChar).Value = jpo.PaymentMethod;
                    cmd.Parameters.Add("@DeliveryMethod", System.Data.SqlDbType.VarChar).Value = jpo.DeliveryMethod;
                    cmd.Parameters.Add("@DeliveryTerms", System.Data.SqlDbType.VarChar).Value = jpo.DeliveryTerms;
                    cmd.Parameters.Add("@DeliveryLocation", System.Data.SqlDbType.VarChar).Value = jpo.DeliveryLocation;
                    cmd.Parameters.Add("@POComments", System.Data.SqlDbType.VarChar).Value = jpo.POComments;
                    cmd.Parameters.Add("@Responsible", System.Data.SqlDbType.VarChar).Value = jpo.Responsible;
                    cmd.Parameters.Add("@Responsible2", System.Data.SqlDbType.VarChar).Value = jpo.Responsible2;
                    cmd.Parameters.Add("@DiscountAmount", System.Data.SqlDbType.Float).Value = jpo.DiscountAmount;
                    cmd.Parameters.Add("@Status", System.Data.SqlDbType.Char).Value = jpo.Status;
                    cmd.Parameters.Add("@Voucher_Type", System.Data.SqlDbType.VarChar).Value = jpo.Voucher_Type;
                    cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime).Value = jpo.AddedOn;
                    cmd.Parameters.Add("@AgressoRowId", System.Data.SqlDbType.BigInt).Value = jpo.AgressoRowId;
                    cmd.Parameters.Add("@TotalLineItems", System.Data.SqlDbType.Int).Value = jpo.TotalLineItems;
                    cmd.Parameters.Add("@ExecutionID", System.Data.SqlDbType.VarChar).Value = jpo.ExecutionID;
                    cmd.Parameters.Add("@AgressoPoID", System.Data.SqlDbType.BigInt).Value = jpo.AgressoPoID;
                    cmd.Parameters.Add("@AgressoPODate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@AgressoApvdDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();

                    foreach (JPODetails podet in jpo.Details)
                    {
                        cmd.CommandText = WinSDSSQL.InsertPODetailInterface();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@jPODetailsID", System.Data.SqlDbType.Int ).Value = podet.jPODetailsID ;
                        cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.VarChar ).Value = podet.PoNo ;
                        cmd.Parameters.Add("@PoLineItemNo", System.Data.SqlDbType.Int ).Value = podet.PoLineItemNo ;
                        cmd.Parameters.Add("@ItemCode", System.Data.SqlDbType.VarChar ).Value = podet.ItemCode ;
                        cmd.Parameters.Add("@ItemDetail", System.Data.SqlDbType.VarChar ).Value = podet.ItemDetail ;
                        cmd.Parameters.Add("@UOM", System.Data.SqlDbType.VarChar ).Value = podet.UOM ;
                        cmd.Parameters.Add("@Packing", System.Data.SqlDbType.VarChar ).Value = podet.Packing ;
                        cmd.Parameters.Add("@OrdQty", System.Data.SqlDbType.Float ).Value = podet.OrdQty ;
                        cmd.Parameters.Add("@UnitPrice", System.Data.SqlDbType.Float ).Value = podet.UnitPrice ;
                        cmd.Parameters.Add("@ReadinessDate", System.Data.SqlDbType.DateTime ).Value = podet.ReadinessDate ;
                        cmd.Parameters.Add("@AccountCode", System.Data.SqlDbType.VarChar ).Value = podet.AccountCode ;
                        cmd.Parameters.Add("@ItemRemarks", System.Data.SqlDbType.VarChar ).Value = podet.ItemRemarks ;
                        cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime ).Value = podet.AddedOn ;
                        cmd.Parameters.Add("@insert_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                }
                else
                {
                    if (po.remarks.Trim() != remarks.Trim())//if remark/batch is different
                    {
                        cmd.CommandText = WinSDSSQL.InsertPOInterface();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@jPOheaderID", System.Data.SqlDbType.Int).Value = id;
                        cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.NChar).Value = pickeupflag;
                        cmd.Parameters.Add("@PickedUpAt", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@remarks", System.Data.SqlDbType.NVarChar).Value = remarks;
                        cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = poNo;
                        cmd.Parameters.Add("@PoDate", System.Data.SqlDbType.DateTime).Value = poDate;
                        cmd.Parameters.Add("@Client", System.Data.SqlDbType.VarChar).Value = jpo.Client;
                        cmd.Parameters.Add("@LegalEntity", System.Data.SqlDbType.VarChar).Value = jpo.LegalEntity;
                        cmd.Parameters.Add("@VslCode", System.Data.SqlDbType.VarChar).Value = jpo.VslCode;
                        cmd.Parameters.Add("@Category", System.Data.SqlDbType.VarChar).Value = jpo.Category;
                        cmd.Parameters.Add("@WorkOrderID", System.Data.SqlDbType.VarChar).Value = jpo.WorkOrderID;
                        cmd.Parameters.Add("@VendorID", System.Data.SqlDbType.VarChar).Value = jpo.VendorID;
                        cmd.Parameters.Add("@CurrencyCode", System.Data.SqlDbType.VarChar).Value = jpo.CurrencyCode;
                        cmd.Parameters.Add("@CurrencyXrate", System.Data.SqlDbType.Float).Value = jpo.CurrencyXrate;
                        cmd.Parameters.Add("@PaymentTerms", System.Data.SqlDbType.VarChar).Value = jpo.PaymentTerms;
                        cmd.Parameters.Add("@PaymentMethod", System.Data.SqlDbType.VarChar).Value = jpo.PaymentMethod;
                        cmd.Parameters.Add("@DeliveryMethod", System.Data.SqlDbType.VarChar).Value = jpo.DeliveryMethod;
                        cmd.Parameters.Add("@DeliveryTerms", System.Data.SqlDbType.VarChar).Value = jpo.DeliveryTerms;
                        cmd.Parameters.Add("@DeliveryLocation", System.Data.SqlDbType.VarChar).Value = jpo.DeliveryLocation;
                        cmd.Parameters.Add("@POComments", System.Data.SqlDbType.VarChar).Value = jpo.POComments;
                        cmd.Parameters.Add("@Responsible", System.Data.SqlDbType.VarChar).Value = jpo.Responsible;
                        cmd.Parameters.Add("@Responsible2", System.Data.SqlDbType.VarChar).Value = jpo.Responsible2;
                        cmd.Parameters.Add("@DiscountAmount", System.Data.SqlDbType.Float).Value = jpo.DiscountAmount;
                        cmd.Parameters.Add("@Status", System.Data.SqlDbType.Char).Value = jpo.Status;
                        cmd.Parameters.Add("@Voucher_Type", System.Data.SqlDbType.VarChar).Value = jpo.Voucher_Type;
                        cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime).Value = jpo.AddedOn;
                        cmd.Parameters.Add("@AgressoRowId", System.Data.SqlDbType.BigInt).Value = jpo.AgressoRowId;
                        cmd.Parameters.Add("@TotalLineItems", System.Data.SqlDbType.Int).Value = jpo.TotalLineItems;
                        cmd.Parameters.Add("@ExecutionID", System.Data.SqlDbType.VarChar).Value = jpo.ExecutionID;
                        cmd.Parameters.Add("@AgressoPoID", System.Data.SqlDbType.BigInt).Value = jpo.AgressoPoID;
                        cmd.Parameters.Add("@AgressoPODate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@AgressoApvdDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                        foreach (JPODetails podet in jpo.Details)
                        {
                            cmd.CommandText = WinSDSSQL.InsertPODetailInterface();
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@jPODetailsID", System.Data.SqlDbType.Int).Value = podet.jPODetailsID;
                            cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.VarChar).Value = podet.PoNo;
                            cmd.Parameters.Add("@PoLineItemNo", System.Data.SqlDbType.Int).Value = podet.PoLineItemNo;
                            cmd.Parameters.Add("@ItemCode", System.Data.SqlDbType.VarChar).Value = podet.ItemCode;
                            cmd.Parameters.Add("@ItemDetail", System.Data.SqlDbType.VarChar).Value = podet.ItemDetail;
                            cmd.Parameters.Add("@UOM", System.Data.SqlDbType.VarChar).Value = podet.UOM;
                            cmd.Parameters.Add("@Packing", System.Data.SqlDbType.VarChar).Value = podet.Packing;
                            cmd.Parameters.Add("@OrdQty", System.Data.SqlDbType.Float).Value = podet.OrdQty;
                            cmd.Parameters.Add("@UnitPrice", System.Data.SqlDbType.Float).Value = podet.UnitPrice;
                            cmd.Parameters.Add("@ReadinessDate", System.Data.SqlDbType.DateTime).Value = podet.ReadinessDate;
                            cmd.Parameters.Add("@AccountCode", System.Data.SqlDbType.VarChar).Value = podet.AccountCode;
                            cmd.Parameters.Add("@ItemRemarks", System.Data.SqlDbType.VarChar).Value = podet.ItemRemarks;
                            cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime).Value = podet.AddedOn;
                            cmd.Parameters.Add("@insert_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        cmd.CommandText = WinSDSSQL.UpdatePOInterface();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@jPOheaderID", System.Data.SqlDbType.Int).Value = id;
                        cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.NChar).Value = pickeupflag;
                        cmd.Parameters.Add("@PickedUpAt", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@remarks", System.Data.SqlDbType.NVarChar).Value = remarks;
                        cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = poNo;
                        cmd.Parameters.Add("@PoDate", System.Data.SqlDbType.DateTime).Value = poDate;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x) { throw x; }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
            }
        }
        public void ExecuteInterfaceAllGRNData()
        {
            m_dataBaseConectivity.WinSDSSqlConnection.Open();
            IList pendingArrive = m_c_GRN_Service.GetAllPendingGRNArrivedID();
            foreach (long arrived_id in pendingArrive)
            {
                SqlCommand cmd = new SqlCommand(WinSDSSQL.GetCountGRNByArrivedID(), m_dataBaseConectivity.WinSDSSqlConnection);
                cmd.Parameters.Add("@ArrivalID", System.Data.SqlDbType.BigInt).Value = arrived_id;
                int found = Convert.ToInt16(cmd.ExecuteScalar());
                if (found > 0)
                {
                    //error
                    m_alertService.CheckAndUpdateAlert(arrived_id.ToString(), AlertType.WIN_SDS_GRN, AlertStatus.PENDING, string.Format("GRN : Duplicate Arrival_ID {0}, Interface Stop",arrived_id), arrived_id.ToString());
                    continue;
                }
                else
                {
                    m_alertService.CheckAndUpdateAlert(arrived_id.ToString(), AlertType.WIN_SDS_GRN, AlertStatus.RESOLVED, "", arrived_id.ToString());
                    try
                    {
                        IList grnList = m_c_GRN_Service.GetAllGRNByArrivedID(arrived_id);
                        if (grnList.Count == 0) continue;

                        foreach (c_GRN grnline in grnList)
                        {
                            cmd.CommandText = WinSDSSQL.InsertJGRNDetails();
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@VslCode", System.Data.SqlDbType.VarChar).Value = grnline.dim_5;
                            cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.VarChar).Value = grnline.ext_ord_ref; //----------error char less-----------
                            cmd.Parameters.Add("@ArrivalID", System.Data.SqlDbType.BigInt).Value = grnline.arrive_id;
                            cmd.Parameters.Add("@PoLineItemNo", System.Data.SqlDbType.Int).Value = grnline.line_no;
                            cmd.Parameters.Add("@QtyReceived", System.Data.SqlDbType.Float).Value = grnline.vow_val;
                            cmd.Parameters.Add("@LineItemComment", System.Data.SqlDbType.VarChar).Value = "";
                            cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                            cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.Int).Value = 0;
                            cmd.Parameters.Add("@AgressoRowId", System.Data.SqlDbType.BigInt).Value = grnline.agrtid;
                            cmd.ExecuteNonQuery();
                        }
                        c_GRN grnHeader = (c_GRN)grnList[0];
                        cmd.CommandText = WinSDSSQL.InsertJGRNHeader();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@VslCode", System.Data.SqlDbType.VarChar).Value = grnHeader.dim_5;
                        cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.VarChar).Value = grnHeader.ext_ord_ref;
                        cmd.Parameters.Add("@ArrivalID", System.Data.SqlDbType.BigInt).Value = grnHeader.arrive_id;
                        cmd.Parameters.Add("@Remark", System.Data.SqlDbType.VarChar).Value = grnHeader.ext_inv_ref;
                        cmd.Parameters.Add("@ReceivedDate", System.Data.SqlDbType.DateTime).Value = grnHeader.deliv_date;
                        cmd.Parameters.Add("@ReceivedBy", System.Data.SqlDbType.VarChar).Value = grnHeader.user_id;
                        cmd.Parameters.Add("@Place", System.Data.SqlDbType.VarChar).Value = grnHeader.warehouse;
                        cmd.Parameters.Add("@GRNComments", System.Data.SqlDbType.VarChar).Value = DateTime.Now;
                        cmd.Parameters.Add("@AddedOn", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@PickedUpFlag", System.Data.SqlDbType.Int).Value = 0;
                        cmd.Parameters.Add("@ResultDetails", System.Data.SqlDbType.VarChar).Value = "";
                        cmd.Parameters.Add("@ExecutionID", System.Data.SqlDbType.VarChar).Value = grnHeader.ExecutionID;
                        cmd.ExecuteNonQuery();

                        m_c_GRN_Service.UpdatePickedUpFlagResultByArriveID(arrived_id, 1, "GRN record pushed to WinSDS successfully.");
                    }
                    catch (Exception x)
                    {
                        m_alertService.CheckAndUpdateAlert(arrived_id.ToString(), AlertType.WIN_SDS_GRN, AlertStatus.PENDING, string.Format(x.Message + " GRN Fail to add WinSDS arrival_id: {0}, Interface Stop", arrived_id), arrived_id.ToString());
                    }
                }
            }
            m_dataBaseConectivity.WinSDSSqlConnection.Close();
        }
        public IList GetAllPendingWinSDSPO(string p)
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllPendingWinSDSPO(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new i_jPOHeader(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingWinSDSPO()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllPendingWinSDSPO();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new i_jPOHeader(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public int GetAllPendingWinSDSPOCount()
        {
            int commissionInvoices = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllPendingWinSDSPOCount();
                commissionInvoices = Convert.ToInt32(cmd.ExecuteScalar());
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void UpdatePOID()
        {
            try
            {
                IList nullPOIDs = new ArrayList();
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllNullPOId();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    nullPOIDs.Add(new jPOHeader(rd));
                rd.Close();
                foreach (jPOHeader hdr in nullPOIDs)
                {
                    apoheader apohdr = null;
                    cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                    cmd.CommandText = WinSDSSQL.GetApoHeaderByPOnumber();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@ext_ord_ref", System.Data.SqlDbType.NVarChar).Value = hdr.PoNo;
                    cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = hdr.Client;
                    rd = cmd.ExecuteReader();
                    while (rd.Read())
                        apohdr = new apoheader(rd);
                    rd.Close();
                    if (apohdr != null)
                    {
                        cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                        cmd.CommandText = WinSDSSQL.UpdatePOID();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = hdr.PoNo;
                        cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = hdr.Client;
                        cmd.Parameters.Add("@AgressoPOID", System.Data.SqlDbType.BigInt).Value = apohdr.order_id;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }
        public void UpdatePOIDMiddleLayer()
        {
            try
            {
                IList nullPOIDs = new ArrayList();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllNullPOIdMiddleLayer();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    nullPOIDs.Add(new jPOHeader(rd,true));
                rd.Close();

                foreach (jPOHeader hdr in nullPOIDs)
                {
                    jPOHeader apohdr = null;
                    cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                    cmd.CommandText = WinSDSSQL.GetjPOHeaderByPOnumber();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = hdr.PoNo;
                    cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = hdr.Client;
                    rd = cmd.ExecuteReader();
                    while (rd.Read())
                        apohdr = new jPOHeader(rd,true);
                    rd.Close();
                    if (apohdr != null)
                    {
                        if (apohdr.AgressoPoID != 0)
                        {
                            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                            cmd.CommandText = WinSDSSQL.UpdatePOIDMiddleLayer();
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@PoNo", System.Data.SqlDbType.NVarChar).Value = hdr.PoNo;
                            cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = hdr.Client;
                            cmd.Parameters.Add("@AgressoPOID", System.Data.SqlDbType.BigInt).Value = apohdr.AgressoPoID;
                            cmd.Parameters.Add("@AgressoApvdDate", System.Data.SqlDbType.DateTime).Value = apohdr.AgressoApvdDate;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }
        public void UpdateAlgbatchinputRemark()
        {
            try
            {
                IList notnullPOIDs = new ArrayList();
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllNotNullPOId();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    notnullPOIDs.Add(new jPOHeader(rd));
                rd.Close();
                foreach (jPOHeader hdr in notnullPOIDs)
                {
                    if (!m_ud_algbatchinput_remarkService.IsExist(hdr.AgressoPoID))
                        m_ud_algbatchinput_remarkService.Insert(hdr.Client, "", hdr.AgressoPoID, hdr.PoNo, hdr.PO_Remark);
                }
            }
            catch (Exception x)
            {
                if (!(x.Message.Trim() == "Invalid column name 'PO_Remark'."))
                    throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }
        public void UpdatePOApprovedDate()
        {
            try
            {
                IList nullPOIDs = new ArrayList();
                m_dataBaseConectivity.WinSDSSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                cmd.CommandText = WinSDSSQL.GetAllNullApproveDatePO();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    nullPOIDs.Add(new jPOHeader(rd, true));
                rd.Close();
                foreach (jPOHeader hdr in nullPOIDs)
                {
                    awftaskfin appdate = null;
                    cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                    cmd.CommandText = WinSDSSQL.GetApprovedDateByPOnumber();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@apoid", System.Data.SqlDbType.NVarChar).Value = hdr.AgressoPoID;
                    cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = hdr.Client;
                    cmd.CommandTimeout = 60;
                    rd = cmd.ExecuteReader();
                    while (rd.Read())
                        appdate = new awftaskfin(rd);
                    rd.Close();
                    if (appdate != null)
                    {
                        cmd.Connection = m_dataBaseConectivity.WinSDSSqlConnection;
                        cmd.CommandText = WinSDSSQL.UpdateApprovedDatePO();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@agressopoid", System.Data.SqlDbType.BigInt).Value = hdr.AgressoPoID;
                        cmd.Parameters.Add("@client", System.Data.SqlDbType.NVarChar).Value = hdr.Client;
                        cmd.Parameters.Add("@Agressoapvddate", System.Data.SqlDbType.DateTime).Value = appdate.apr_date;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.WinSDSSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }

        public void InsertWinSDSAccountMapping(i_winsds_itemcode_account_mapping acc)
        {
            try 
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.InsertWinSDSAccountMapping();
                cmd.Parameters.Add("@itemCode", System.Data.SqlDbType.NVarChar).Value = acc.itemCode;
                cmd.Parameters.Add("@account", System.Data.SqlDbType.NVarChar).Value = acc.account;
                cmd.Parameters.Add("@dim_2", System.Data.SqlDbType.NVarChar).Value = acc.dim_2;
                cmd.Parameters.Add("@created_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@modified_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void UpdateWinSDSAccountMapping(i_winsds_itemcode_account_mapping acc)
        {
            try
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.UpdateWinSDSAccountMapping();
                cmd.Parameters.Add("@itemCode", System.Data.SqlDbType.NVarChar).Value = acc.itemCode;
                cmd.Parameters.Add("@account", System.Data.SqlDbType.NVarChar).Value = acc.account;
                cmd.Parameters.Add("@dim_2", System.Data.SqlDbType.NVarChar).Value = acc.dim_2;
                cmd.Parameters.Add("@modified_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = acc.id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void DeleteWinSDSAccountMapping(int id)
        {
            try
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.DeleteWinSDSAccountMapping();
                cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public i_winsds_itemcode_account_mapping FindByItemCOdeWinSDSAccountMapping(string itemCode)
        {
            try
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Open();
                i_winsds_itemcode_account_mapping result = null;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.FindByItemCodeWinSDSAccountMapping();
                cmd.Parameters.Add("@itemCode", System.Data.SqlDbType.NVarChar).Value = itemCode;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result = new i_winsds_itemcode_account_mapping(rd);
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList FindAllWinSDSAccountMapping()
        {
            try
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Open();
                IList result = new ArrayList();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = WinSDSSQL.FindAllWinSDSAccountMapping();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new i_winsds_itemcode_account_mapping(rd));
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_winsdsInterfacedataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
